import java.sql.*;
import java.util.Set;

public class MysqlTushu {
    private final String url = "jdbc:mysql://localhost:3306/tushu?useSSL=false&serverTimezone=UTC";
    private Connection conn = null;
    MysqlTushu(String user,String password) throws SQLException {
        try {
            // 使用ClassLoader标准API加载库
            ClassLoader.getSystemClassLoader().loadClass("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
        }

        conn = DriverManager.getConnection(url, user, password);
    }

    public void sqlInit(Set<Massage> set,Set<Integer> idSet){
        String selectsql = "select * from books";
        try (PreparedStatement p = conn.prepareStatement(selectsql)) {
            try (ResultSet re = p.executeQuery()) {
                while (re.next()) {
                    String b_name = re.getString("b_name");
                    String author = re.getString("author");
                    int price = re.getInt("price");
                    int id = re.getInt("id");
                    boolean state = re.getBoolean("state");
                    set.add(new Massage(b_name,author,price,id,state));
                    idSet.add(id);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public void insert(Massage massage){
        String insertSql = "insert into books (b_name, author,price,id,state) values (?,?,?,?,false)";
        try (PreparedStatement insert = conn.prepareStatement(insertSql)) {
            insert.setString(1, massage.getBookName());
            insert.setString(2, massage.getAuthorName());
            insert.setInt(3, massage.getPrice());
            insert.setInt(4, massage.getId());
            insert.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void erase(int id) {
            String deleteSql = "DELETE FROM books WHERE id = ?";
            try (PreparedStatement del = conn.prepareStatement(deleteSql)) {
                del.setInt(1, id);
                int rowsDeleted = del.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
}